import numpy as np

import 去除企宽

if __name__ == '__main__':
    import pandas as pd

    # df = pd.read_excel("D:\\家宽\\2025-03\\【2月累计】智慧装维.xlsx",
    #                    usecols=['工单号', '建单时间', '宽带账号', '具体类别', '原因分类 ', '家宽小区ID', '家宽小区名称',
    #                             '分公司'])
    # df.to_csv('/temp/t.csv', index=False)

    df = pd.read_csv("C:\\Users\\11314\\Desktop\\匹配上一次掉线时间.csv")
    df2 = 去除企宽.get_df('0201_0228',
                          ['account', 'level', 'alarm_name', 'alarm_source', 'additional', 'happen_time', 'clear_time',
                           'confirm_time', 'olt_pon'])
    df3 = 去除企宽.get_df('0101_0131',
                          ['account', 'level', 'alarm_name', 'alarm_source', 'additional', 'happen_time', 'clear_time',
                           'confirm_time', 'olt_pon'])
    df4 = 去除企宽.get_df('0301_0331',
                          ['account', 'level', 'alarm_name', 'alarm_source', 'additional', 'happen_time', 'clear_time',
                           'confirm_time', 'olt_pon'])
    df5 = 去除企宽.get_df('0401_0430',
                          ['account', 'level', 'alarm_name', 'alarm_source', 'additional', 'happen_time', 'clear_time',
                           'confirm_time', 'olt_pon'])
    df2 = pd.concat([df2, df3, df4, df5])
    df2.rename(columns={
        'account': '宽带账号'
    }, inplace=True)
    df = df.merge(df2, on='宽带账号', how='left')

    df['建单时间'] = pd.to_datetime(df['建单时间'])
    df['happen_time'] = pd.to_datetime(df['happen_time'])
    # 筛选出半个小时内的数据
    df.loc[
        ((df['建单时间'] < df['happen_time']) | (df['建单时间'] > (df['happen_time'] + pd.Timedelta(hours=72)))),
        ['level', 'alarm_name', 'alarm_source', 'additional', 'happen_time', 'clear_time',
         'confirm_time', 'olt_pon']] = np.nan
    df = df.drop_duplicates()
    df.to_excel('/temp/t2.xlsx', index=False)
